﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;  // for exe path of application
using MySql.Data.MySqlClient;     // for connection with database

namespace ExitEventApp
{
    public class DataHelper
    {
        private MySqlConnection connection;

        /* CONSTRUCTOR*/
        public DataHelper()
        {
            String connectionInfo = "server=athena01.fhict.local;" +
                             "database=dbi269964;" + // your database name
                             "user id=dbi269964;" + // your user id
                             "password=ztauJSorfR;" + // your password
                             "connect timeout=30;";
            connection = new MySqlConnection(connectionInfo);

        }

        /*
         * returns a list of rented items for a specific client id;
         * if no item will be found, it will return an empty list
         * */
        public List<RentedItem> loadRentedItems(long clientId)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "SELECT h.ProductId,h.ProductName,h.UnitPrice*(l.EndLoan-l.StartLoan),h.Deposit,l.HasReturned FROM product_hired h,hire_receipt_line l,hire_receipt r WHERE h.ProductID=l.ProductID AND l.ReceiptNo=r.ReceiptNo AND r.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<RentedItem> temp;
            temp = new List<RentedItem>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                int productId;
                String productName;
                double unitPrice;
                int deposit;
                bool isReturned;
                while (reader.Read())
                {
                    productId = Convert.ToInt32(reader["ProductId"]);
                    productName = Convert.ToString(reader["ProductName"]);
                    unitPrice = Convert.ToDouble(reader[2]);
                    deposit = Convert.ToInt32(reader[3]);
                    String ret = Convert.ToString(reader["HasReturned"]);
                    if (ret == "y") { isReturned = true; } else { isReturned = false; }
                    temp.Add(new RentedItem(productId, productName, unitPrice, deposit, isReturned));
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }

        /* it will set the status of a client and also, it will set the Date of the exit of the client
         * it returns -1 if the operation didn't succeed, or a positive number otherwise
         * */
        public int changeStatusClient(long clientId, DateTime lastE)
        {
            String visitorId = Convert.ToString(clientId);
            String last = lastE.ToString("yyyy-MM-dd hh:mm:ss");
            String sql = "UPDATE EVENT_STATUS SET HasLeftEvent='y',LastExit='" + last + "'  WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }
        /*
         * it will set the balance of a client to zero
         * return -1 if the operation didn't succeed or a positive number otherwise*/
        public int changeBalanceClient(long clientId)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "UPDATE EVENT_ACCOUNT SET MoneyDeposited=0  WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }

        /*
         * it will return an object of type Client based on the id
         * if no client will be found, it will return null */
        public Client getClient(long clientId)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "SELECT v.Visitor_id,v.LastName,v.FirstName,v.Email,v.BankAccount,e.MoneyDeposited,s.LastExit FROM visitor v JOIN event_account e ON v.Visitor_Id=e.Visitor_id JOIN event_status s ON v.Visitor_Id=s.Visitor_Id WHERE v.Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int nr = 0;
            List<Client> temp;
            temp = new List<Client>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string name1, name2;
                double balance; String email; String bank;
                long no;
                DateTime lastExit;
                while (reader.Read())
                {
                    no = Convert.ToInt64(reader["Visitor_id"]);
                    name1 = Convert.ToString(reader["LastName"]);
                    name2 = Convert.ToString(reader["FirstName"]);
                    email = Convert.ToString(reader["Email"]);
                    bank = Convert.ToString(reader["BankAccount"]);
                    balance = Convert.ToDouble(reader["MoneyDeposited"]);
                    lastExit = Convert.ToDateTime(reader["LastExit"]);

                    temp.Add(new Client(no, name1, name2, balance, "in", email, lastExit, bank));
                    nr++;
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            if (nr > 0)
            {
                return temp[0];
            }
            else { return null; }
        }
    }
}

